﻿//using NPOI.HSSF.UserModel;
//using NPOI.SS.Formula.Eval;
//using NPOI.SS.UserModel;
//using NPOI.XSSF.UserModel;
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace Magicodes.Admin.Web
{
    public static class ExcelHelper
    {

        public static DataSet ExcelReader(string filePath)
        {

            using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {

                // Auto-detect format, supports:
                //  - Binary Excel files (2.0-2003 format; *.xls)
                //  - OpenXml Excel files (2007 format; *.xlsx)
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {

                    // Choose one of either 1 or 2:

                    // 1. Use the reader methods
                    do
                    {
                        while (reader.Read())
                        {
                            // reader.GetDouble(0);
                        }
                    } while (reader.NextResult());

                    // 2. Use the AsDataSet extension method
                    var result = reader.AsDataSet();
                    return result;
                    // The result of each spreadsheet is in result.Tables
                }
            }
        }

        public static DataSet ExcelReader(Stream stream,bool useHeaderRow=false)  {
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {

                // Choose one of either 1 or 2:

                // 1. Use the reader methods
                do
                {
                    while (reader.Read())
                    {
                        // reader.GetDouble(0);
                    }
                } while (reader.NextResult());

                // 2. Use the AsDataSet extension method
                var result = reader.AsDataSet(new ExcelDataSetConfiguration {
                    ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                    {
                         UseHeaderRow = useHeaderRow, 
                    }
                });



                return result;
                // The result of each spreadsheet is in result.Tables
            }

        }
        public static IReadOnlyList<TEntity> ExcelReaderToEntitys<TEntity>(  this DataTable  dataTable, List<string> columns) where TEntity : class 
        {
            if (dataTable == null) return null;
            if (dataTable.Rows.Count == 0) return new List<TEntity>();

            var lst = new List<TEntity>();
            var plist = new List<System.Reflection.PropertyInfo>(typeof(TEntity).GetProperties());

            
            foreach (DataRow item in dataTable.Rows)
            {
                TEntity t = System.Activator.CreateInstance<TEntity>();

                 
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    var name = columns[i];

                    PropertyInfo info = plist.Find(p => p.Name == name);
                    if (info != null)
                    {
                        var value = item[i] + "";
                        
                        if (info.PropertyType == typeof(int))
                        {
                            var newValue = 0;
                            int.TryParse(value, out newValue);
                            info.SetValue(t, newValue, null);
                        }
                        else if (info.PropertyType == typeof(decimal))
                        {
                            decimal newValue =0;
                            decimal.TryParse(value, out newValue);
                            info.SetValue(t, newValue, null);
                        }
                        else if (info.PropertyType == typeof(double))
                        {
                            double newValue = 0;
                            double.TryParse(value, out newValue);
                            info.SetValue(t, newValue, null);
                        }
                        else if (info.PropertyType == typeof(float))
                        {
                            float newValue = 0;
                            float.TryParse(value, out newValue);
                            info.SetValue(t, newValue, null);
                        }
                        else if (info.PropertyType == typeof(DateTime))
                        {
                            var newValue = Convert.ToDateTime(value);
                            if(newValue>new DateTime(2018,1,1))
                            info.SetValue(t, Convert.ToDateTime(value) , null);
                        }
                        else
                        { 
                            info.SetValue(t, Convert.ChangeType(value, info.PropertyType), null);
                        }

                      
                    }
                }
                lst.Add(t);
            }

            return lst;
        }


    }
}
